import xlwt 
import xlrd
import os
from xlutils.copy import copy
import re


def read_xls(xls_path, lookup_path):
    lookup_work_book = xlrd.open_workbook(lookup_path)
    lookup_info_sheet = lookup_work_book.sheet_by_index(0)

    # print(lookup_info_sheet.row_values(12238))
    # print(lookup_info_sheet.row_values(122))

    lookup_dict = dict()
    for i in range(1, lookup_info_sheet.nrows):
        value = lookup_info_sheet.row_values(i)
        lookup_dict[value[0]] = value

    work_book = xlrd.open_workbook(xls_path)
    # print(work_book.sheet_names())

    # sheet2_name = work_book.sheet_names()[0]
    fill_info_sheet = work_book.sheet_by_index(0)
    print(fill_info_sheet.name, fill_info_sheet.nrows, fill_info_sheet.ncols)

    all_length = fill_info_sheet.nrows
    all_cols = fill_info_sheet.ncols
    all_info = []
    for idx in range(1, all_length):
        line_info = fill_info_sheet.row_values(idx)
        if line_info[0] == "":
            continue
        try:
            number = int(line_info[0])
        except:
            print(idx, line_info[0])
            continue
        line_info[0] = number
        total_info = lookup_dict[str(number)]
        for j in range(all_cols):
            if line_info[j] == "":
                line_info[j] = total_info[j]
        all_info.append(line_info)

    print(all_info[100])
    print(all_info[1000])
    print(all_info[10000])


    for j in range(all_cols):
        save_path = os.path.dirname(lookup_path) + str(j) + '.txt'
        with open(save_path, 'w') as wf:
            for line in all_info:
                wf.write(str(line[j]) + "\n")
    


def read_file(file_url):
    try:
        data = xlrd.open_workbook(file_url)
        return data
    except Exception as e:
        print(str(e))

# def get_data(workbook,exist_list, start_idx=2, by_index=0):
def get_data(workbook,exist_list, start_idx=3, by_index=0):

    table = workbook.sheet_by_index(by_index)  # 获得表格
    total_rows = table.nrows  # 拿到总共行数
    total_cols = table.ncols  # 拿到总共行数
    # columns = table.row_values(column_name)  # 某一行数据 ['姓名', '用户名', '联系方式', '密码']
    excel_list = {}
    for one_row in range(start_idx, total_rows):  # 也就是从Excel第二行开始，第一行表头不算

        row = table.row_values(one_row)
        # if (row and row[9] != "") and (row[2] not in exist_list):
        if (row and row[9] != "") and (row[2] not in exist_list):

            # excel_list[row[2]] = [row[3], row[4], row[5], row[6]]
            excel_list[row[2]] = [row[9], row[10], row[11], row[12]]

            exist_list.append(row[2])
    return excel_list, exist_list

# def save_xls(save_path, data_lists,start_idx=2, by_index=0):
def save_xls(save_path, data_lists,start_idx=3, by_index=0):
    workbook = xlrd.open_workbook(save_path)
    
    table = workbook.sheet_by_index(by_index)  # 获得表格
    total_rows = table.nrows  # 拿到总共行数

    write_workbook = copy(wb=workbook) 
    write_excel = write_workbook.get_sheet(0)
    

    for one_row in range(start_idx, total_rows):
        row_values = table.row_values(one_row)
        stu_id = row_values[2]
        data_list = data_lists.get(stu_id, None)
        if data_list is None:
            print(stu_id, "is None")
            continue
        
        # for col_idx in range(3, 7):
        #     write_excel.write(one_row,col_idx,data_list[col_idx-3])
        for col_idx in range(9, 13):
            write_excel.write(one_row,col_idx,data_list[col_idx-9])

    write_workbook.save(save_path)


def write_data(xls_path):
    book = xlwt.Workbook() 
    sheet = book.add_sheet('ocr', cell_overwrite_ok=True)

    font = xlwt.Font()
    font.blod = True

    pattern_top = xlwt.Pattern()
    pattern_top.pattern = xlwt.Pattern.SOLID_PATTERN
    pattern_top.pattern_fore_colour = 5

    style = xlwt.XFStyle()
    style.font = font
    style.pattern = pattern_top
    sheet.write_merge(1, 2, 0, 3, 'Second Merge', style)

    # for one_row in range(start_idx, total_rows):
    #     row_values = table.row_values(one_row)
    #     stu_id = row_values[2]
    #     data_list = data_lists.get(stu_id, None)
    #     if data_list is None:
    #         print(stu_id, "is None")
    #         continue
        
    #     # for col_idx in range(3, 7):
    #     #     write_excel.write(one_row,col_idx,data_list[col_idx-3])
    #     for col_idx in range(9, 13):
    #         sheet.write(one_row,col_idx,data_list[col_idx-9])

    book.save(xls_path)


def main(file_dir,save_path, file_type=0):
    data_lists = {}

    reg = re.compile(str(file_type))
    exist_list = []
    for p in os.listdir(file_dir):
        if len(reg.findall(p)) > 0:
            print(p)

            data = read_file(file_dir+os.sep+p)

            excel_list, exist_list_temp = get_data(data, exist_list)
            exist_list = exist_list_temp
            print(excel_list)
            # print(excel_list)
            data_lists.update(excel_list)
    print(data_lists)
    save_xls(save_path, data_lists)

def read_xlsx_data(xlsx_path, start_idx=1, by_index=0):
    workbook = read_file(xlsx_path)

    table = workbook.sheet_by_index(by_index)  # 获得表格
    total_rows = table.nrows  # 拿到总共行数
    total_cols = table.ncols  # 拿到总共行数
    # columns = table.row_values(column_name)  # 某一行数据 ['姓名', '用户名', '联系方式', '密码']
    excel_list = {}
    for one_row in range(start_idx, total_rows):  # 也就是从Excel第二行开始，第一行表头不算

        row = table.row_values(one_row)
        # if (row and row[9] != "") and (row[2] not in exist_list):
        if row[3] != "":
            print(row[2])

if __name__ == "__main__":
    # file_dir = r"C:\Users\swls\Desktop\2"
    # file_type = 13
    # save_path = r"C:\Users\swls\Desktop\out\13终结性评价.xls"
    # main(file_dir, save_path, file_type)

    # xlsx_path = r"E:\work\data\data\2020_03\威瑟兰斯样本测试\小票样本.xlsx"
    # read_xlsx_data(xlsx_path)

    xls_path = '/home/swls/zzcopy/out_info.xlsx'
    lookup_path = '/home/swls/zzcopy/infortation.xlsx'
    read_xls(xls_path, lookup_path)